package com.itranswarp.learnjava.dao;

import com.itranswarp.learnjava.utils.DBUtil;
import com.itranswarp.learnjava.bean.User;
import com.itranswarp.learnjava.bean.PageResult;
import java.util.ArrayList;

import java.sql.*;

public class UserDAO {

    public User getUser(String username, String password) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "select * from users where username = ? and password = ? and status = 1";
        User user = null;
        try(PreparedStatement ps = conn.prepareStatement(
                sql,
                Statement.RETURN_GENERATED_KEYS // 返回数据库自动生成的key
        )){
            ps.setString(1, username);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    user = extractRow(rs);
                }
            }
        }
        return user;
    }

    public User addUser (String username, String password, String phone, String email){
        Connection conn = DBUtil.getConnection();
        String sql = "INSERT INTO users (username, password, phone,email) VALUES (?, ?, ?, ?)";
        User user = null;
        try(PreparedStatement ps = conn.prepareStatement(
                sql,
                Statement.RETURN_GENERATED_KEYS // 返回数据库自动生成的key
        )){
            ps.setString(1, username);
            ps.setString(2, password);
            ps.setString(3, phone);
            ps.setString(4, email);
            int affectedRows = ps.executeUpdate(); // ✅ 正确方式
            if (affectedRows == 0) {
                throw new SQLException("Insert failed, no rows affected.");
            }

            // 返回插入后的 User 对象，仅包含生成的 ID
            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) {
                    user = new User(email, username, rs.getLong(1), phone, null, null);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return user;
    }

    public User updateUser (long id, String username, String phone, String email){
        Connection conn = DBUtil.getConnection();
        String sql = "UPDATE users SET username = ?,phone = ?,email = ? WHERE id = ?;";
        User user = null;
        try(PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setString(1, username);
            ps.setString(2, phone);
            ps.setString(3, email);
            ps.setLong(4, id);
            int affectedRows = ps.executeUpdate(); // ✅ 正确方式
            if (affectedRows != 0) {
                user = new User(email, username, id, phone, null, null);
            } else {
                System.out.println("Update failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return user;
    }

    public boolean removeUser(long id){
        Connection conn = DBUtil.getConnection();
        String sql = "DELETE FROM users WHERE id = ?";
        try(PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setLong(1, id);
            int affectedRows = ps.executeUpdate(); // ✅ 正确方式
            return affectedRows > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    static User extractRow(ResultSet rs) throws SQLException {
        var user = new User(rs.getString("email"), rs.getString("username"), rs.getLong("id"), rs.getString("phone"), rs.getString("created_at"), rs.getString("update_at"));
        return user;
    }

    public PageResult<User> getAllUsers( int offset, int pageSize) {
        ArrayList<User> users = new ArrayList<>();
        PageResult<User> result = new PageResult<>();

        try {
            Connection conn = DBUtil.getConnection();
            String sql = "SELECT * FROM users LIMIT ?, ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, offset *pageSize);
            stmt.setInt(2, pageSize);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                users.add(extractRow(rs));
            }
            rs.close();
            stmt.close();
            result.pageSize = pageSize;
            result.currentPage = offset + 1;
            result.records = users;
            result.totalRecords = getUserCount();
            result.totalPages = (int) Math.ceil((double) result.totalRecords / pageSize);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getUserCount(){
        Connection conn = DBUtil.getConnection();
        String sql = "SELECT COUNT(*) FROM users";
        int totalRecords=0;
        try(PreparedStatement ps = conn.prepareStatement(sql)){
            ResultSet rs = ps.executeQuery(); // ✅ 正确方式
            if(rs.next()){
                totalRecords = rs.getInt(1);
            }
            rs.close();
            ps.close();
            return totalRecords;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
